package com.clei.utils;

import com.clei.annotation.Excel;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * excel 工具类
 *
 * @author Y
 * @date 2021-12-29
 */
public final class ExcelUtil {

    /**
     * 默认文件后缀
     */
    private static final String XLSX = ".xlsx";

    /**
     * 列数据为null时的填充值
     */
    private static final String EMPTY = "";

    /**
     * Workbook
     */
    private static Workbook curWorkbook;

    /**
     * 表达式计算器
     */
    private static FormulaEvaluator formulaEvaluator;

    private ExcelUtil() {
    }

    /**
     * 将数据写入excel文件
     *
     * @param outPath  输出文件路径
     * @param fileName 文件名称
     * @param clazz    数据Class
     * @param data     数据
     * @throws java.lang.Exception Exception
     */
    public static void write(String outPath, String fileName, Class<?> clazz, List<?> data) throws Exception {
        ExcelEntity excelEntity = new ExcelEntity(clazz);
        // 标题
        List<String> titles = excelEntity.getTitles();
        // 属性
        List<Field> keys = excelEntity.getKeys();

        // 创建excel
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook());
        SXSSFSheet sheet = workbook.createSheet(fileName);

        // 写入标题
        SXSSFRow row = sheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            row.createCell(i).setCellValue(titles.get(i));
        }

        // 写入数据
        int rowNum = 1;
        for (Object obj : data) {
            row = sheet.createRow(rowNum++);
            int i = 0;
            for (Field field : keys) {
                Object o = field.get(obj);
                String cellValue = null == o ? EMPTY : String.valueOf(o);
                row.createCell(i++).setCellValue(cellValue);
            }
        }

        // 写入文件
        try (FileOutputStream fos = new FileOutputStream(outPath + File.separator + fileName + XLSX)) {
            workbook.write(fos);
        } catch (Exception e) {
            PrintUtil.log("导出excel出错 outPath : {}, fileName : {}", outPath, fileName, e);
        } finally {
            workbook.close();
        }
    }

    /**
     * 将数据写入excel文件
     *
     * @param outPath  输出文件路径
     * @param fileName 文件名称
     * @param data     数据
     * @throws java.lang.Exception Exception
     */
    public static void write(String outPath, String fileName, List<List<String>> data) throws Exception {
        // 创建excel
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook());
        SXSSFSheet sheet = workbook.createSheet(fileName);
        // 写入数据
        int rowNum = 0;
        for (List<String> rowData : data) {
            SXSSFRow row = sheet.createRow(rowNum++);
            int cellNum = 0;
            for (String item : rowData) {
                row.createCell(cellNum++).setCellValue(item);
            }
        }
        // 写入文件
        try (FileOutputStream fos = new FileOutputStream(outPath + File.separator + fileName + XLSX)) {
            workbook.write(fos);
        } catch (Exception e) {
            PrintUtil.log("导出excel出错 outPath : {}, fileName : {}", outPath, fileName, e);
        } finally {
            workbook.close();
        }
    }

    /**
     * 将数据写入excel文件
     *
     * @param filePath filePath
     * @param clazz    数据Class
     * @param <T>      泛型
     * @return List
     * @throws java.lang.Exception Exception
     */
    public static <T> List<T> read(String filePath, Class<T> clazz) throws Exception {
        ExcelEntity excelEntity = new ExcelEntity(clazz);
        // 属性
        List<Field> keys = excelEntity.getKeys();
        // 转换器
        List<Function<String, Object>> functionList = keys.stream().map(f -> convert(f.getType())).collect(Collectors.toList());
        // 从第二行开始，第一行是标题
        int rowNum = 1;
        // res
        List<T> resList = null;
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            curWorkbook = workbook;
            // 对应关系
            setIndexFieldMap(sheet.getRow(0), excelEntity);
            // 提取器
            int cellNum = excelEntity.getIndexFieldMap().keySet().stream().max(Comparator.comparingInt(i -> i)).map(v -> v + 1).orElse(0);
            Function<Cell, String>[] valueExtractorArr = new Function[cellNum];

            int lastRowNum = sheet.getLastRowNum() + 1;
            resList = new ArrayList<>(lastRowNum);
            for (int i = rowNum; i < lastRowNum; i++) {
                Row row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                T instance = clazz.newInstance();
                // 仅遍历有对应属性的列
                for (Map.Entry<Integer, Field> entry : excelEntity.getIndexFieldMap().entrySet()) {
                    Integer index = entry.getKey();
                    Field f = entry.getValue();
                    Cell cell = row.getCell(index);
                    if (null != cell) {
                        // 值提取器
                        Function<Cell, String> valueExtractor = valueExtractorArr[index];
                        if (null == valueExtractor) {
                            valueExtractor = getCellValueExtractor(cell);
                            valueExtractorArr[index] = valueExtractor;
                        }
                        String value = valueExtractor.apply(cell);
                        if (StringUtil.isBlank(value)) {
                            f.set(instance, value);
                        } else {
                            Function<String, Object> function = functionList.get(index);
                            f.set(instance, function.apply(value));
                        }
                    }
                }
                resList.add(instance);
            }
        } catch (Exception e) {
            PrintUtil.log("读取excel出错", e);
        }
        return resList;
    }

    /**
     * 将数据写入excel文件
     *
     * @param filePath filePath
     * @return List
     * @throws java.lang.Exception Exception
     */
    public static List<List<String>> read(String filePath) throws Exception {
        // res
        List<List<String>> resList = null;
        try (FileInputStream fis = new FileInputStream(filePath);
             Workbook workbook = WorkbookFactory.create(fis)) {
            curWorkbook = workbook;
            Sheet sheet = workbook.getSheetAt(0);
            int rowNum = sheet.getLastRowNum() + 1;
            resList = new ArrayList<>(rowNum);
            // 表头读取
            Row titleRow = sheet.getRow(0);
            int cellNum = 0;
            if (null != titleRow) {
                cellNum = titleRow.getLastCellNum();
            }
            List<String> rowData = new ArrayList<>(cellNum);
            for (int i = 0; i < cellNum; i++) {
                Cell cell = titleRow.getCell(i);
                rowData.add(null == cell ? null : cell.getStringCellValue());
            }
            resList.add(rowData);
            // 提取器
            Function<Cell, String>[] valueExtractorArr = new Function[cellNum];
            // 读取数据
            for (int i = 1; i < rowNum; i++) {
                Row row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                rowData = new ArrayList<>(cellNum);
                for (int j = 0; j < cellNum; j++) {
                    Cell cell = row.getCell(j);
                    String value = null;
                    if (null != cell) {
                        // 值提取器
                        Function<Cell, String> valueExtractor = valueExtractorArr[j];
                        if (null == valueExtractor) {
                            valueExtractor = getCellValueExtractor(cell);
                            valueExtractorArr[j] = valueExtractor;
                        }
                        value = valueExtractor.apply(cell);
                    }
                    rowData.add(value);
                }
                resList.add(rowData);
            }
        } catch (Exception e) {
            PrintUtil.log("读取excel出错", e);
        }
        return resList;
    }

    /**
     * 设置列
     * 允许一个excel有n列，class里却只有一部分的属性对应，按name
     *
     * @param row         标题列
     * @param excelEntity ExcelEntity
     */
    private static void setIndexFieldMap(Row row, ExcelEntity excelEntity) {
        Set<String> titleSet = new HashSet<>(excelEntity.getTitles());
        Map<Integer, Field> indexFieldMap = new HashMap<>(titleSet.size());
        int end = row.getLastCellNum();
        for (int i = 0; i < end; i++) {
            Cell cell = row.getCell(i);
            if (null != cell) {
                String value = cell.getStringCellValue();
                if (titleSet.contains(value)) {
                    indexFieldMap.put(i, excelEntity.getFieldMap().get(value));
                }
            }
        }
        excelEntity.setIndexFieldMap(indexFieldMap);
    }

    /**
     * 获取值提取器
     *
     * @param cell 单元格
     * @return 值提取器
     */
    private static Function<Cell, String> getCellValueExtractor(Cell cell) {
        if (null == cell) {
            return null;
        }
        return getCellValueExtractor(cell.getCellType());
    }

    /**
     * 获取值提取器
     *
     * @param cellType 单元格类型
     * @return 值提取器
     */
    private static Function<Cell, String> getCellValueExtractor(CellType cellType) {
        switch (cellType) {
            case NUMERIC:
                return c -> String.valueOf(c.getNumericCellValue());
            case BOOLEAN:
                return c -> String.valueOf(c.getBooleanCellValue());
            case FORMULA:
                if (null == formulaEvaluator) {
                    formulaEvaluator = curWorkbook.getCreationHelper().createFormulaEvaluator();
                }
                return c -> {
                    CellValue cellValue = formulaEvaluator.evaluate(c);
                    CellType ct = cellValue.getCellType();
                    switch (ct) {
                        case STRING:
                            return cellValue.getStringValue();
                        case NUMERIC:
                            return String.valueOf(cellValue.getNumberValue());
                        case BOOLEAN:
                            return String.valueOf(cellValue.getBooleanValue());
                        default:
                            return null;
                    }
                };
            default:
                return Cell::getStringCellValue;
        }
    }

    /**
     * Function String -> T
     *
     * @param type Class type
     * @return Function
     */
    private static Function<String, Object> convert(Class<?> type) {
        if (type == String.class) {
            return s -> s;
        }
        if (type == Byte.class) {
            return Byte::valueOf;
        }
        if (type == Short.class) {
            return Short::valueOf;
        }
        if (type == Integer.class) {
            return Integer::valueOf;
        }
        if (type == Long.class) {
            return Long::valueOf;
        }
        if (type == Double.class) {
            return Double::valueOf;
        }
        if (type == Float.class) {
            return Float::valueOf;
        }
        if (type == Boolean.class) {
            return Boolean::valueOf;
        }
        if (type == Character.class) {
            return s -> s.charAt(0);
        }
        return s -> s;
    }

    private static class ExcelEntity {

        /**
         * 标题
         */
        private final List<String> titles;

        /**
         * 属性
         */
        private final List<Field> keys;

        /**
         * title -> field map
         */
        private final Map<String, Field> fieldMap;

        /**
         * index -> field map
         */
        Map<Integer, Field> indexFieldMap;

        ExcelEntity(Class<?> clazz) {
            // 标题及字段获取
            Field[] fields = clazz.getDeclaredFields();
            List<Excel> columnList = new ArrayList<>(fields.length);
            this.fieldMap = new HashMap<>(fields.length);
            for (Field field : fields) {
                Excel column = field.getAnnotation(Excel.class);
                // 判断是否是static
                // Modifier.isStatic(field.getModifiers());
                if (null != column) {
                    field.setAccessible(Boolean.TRUE);
                    columnList.add(column);
                    this.fieldMap.put(column.name(), field);
                }
            }
            if (CollectionUtil.isEmpty(columnList)) {
                throw new RuntimeException("excel列与字段无对应关系");
            }
            columnList.sort(Comparator.comparingInt(Excel::order));
            // 标题
            this.titles = columnList.stream().map(Excel::name).collect(Collectors.toList());
            // 属性
            this.keys = this.titles.stream().map(this.fieldMap::get).collect(Collectors.toList());
        }

        public List<String> getTitles() {
            return titles;
        }

        public List<Field> getKeys() {
            return keys;
        }

        public Map<String, Field> getFieldMap() {
            return fieldMap;
        }

        public Map<Integer, Field> getIndexFieldMap() {
            return indexFieldMap;
        }

        public void setIndexFieldMap(Map<Integer, Field> indexFieldMap) {
            this.indexFieldMap = indexFieldMap;
        }
    }
}
